﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace LogisticsDAL
{
    public class UserDAL
    {
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="wherelist"></param>
        /// <param name="pramlist"></param>
        /// <returns></returns>
        public  DataTable GvRead(string txtName,string phone,string checktime,string checkDateEnd,
        string Tsex,string delet,string acount,string Email,string roles,string afterTime,string AfterDateEnd,
         int pageIndex,int pageSzie,out int Count)
        {
            string TableName = "View_UserBind";
            string pramary = "UserID";
            string Filed = "1=1";
            if (!string.IsNullOrWhiteSpace(txtName))
            {
                Filed = Filed + " and UserName like '%" + txtName + "%'";
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                Filed = Filed + " and phone like '%" + phone + "%'";
            }
   
            if (!string.IsNullOrWhiteSpace(checktime) && checkDateEnd!="")
            {
                Filed = Filed + " and (CheckInTime>= '" + checktime + "' and CheckInTime<= '"+checkDateEnd+"'";
            }
            if (Tsex == "0" || Tsex == "1")
            {
                Filed = Filed + " and sex='" + Tsex + "'";
            }
            if (!string.IsNullOrWhiteSpace(delet))
            {
                Filed = Filed + " and IsDelete='" + delet + "'";
            }
            if (!string.IsNullOrWhiteSpace(afterTime) && AfterDateEnd!="")
            {
                Filed = Filed + " and (AlterTime>='" + afterTime + "' and AlterTime<='"+AfterDateEnd+"')";
            }
            if (!string.IsNullOrWhiteSpace(acount))
            {
                Filed = Filed + " and Account='" + acount + "'";
            }
            if (!string.IsNullOrWhiteSpace(Email))
            {
                Filed = Filed + " and Email='" + Email + "'";
            }
            if (roles != "0")
            {
                Filed = Filed + " and RoleName='" + roles + "'";
            }
            DataTable da =PROC_Helper.Paging(TableName,pramary,pageIndex,pageSzie,Filed,out Count);
            List<Users> use = new List<Users>();
            //if (da.Rows.Count > 0)
            //{
            //    foreach (DataRow item in da.Rows)
            //    {

            //        use.Add(new Users()
            //        {
            //            UserName = item["UserName"].ToString(),
            //            RoleID = Convert.ToInt32(item["RoleID"]),
            //            sex=Convert.ToInt16(item["sex"]),
            //            Account=item["Account"].ToString(),
            //            phone=item["phone"].ToString(),
            //            Email=item["Email"].ToString(),
            //            rolename=item["RoleName"].ToString(),
            //            CheckInTime=Convert.ToDateTime(item["CheckInTime"]),
            //            AlterTime=Convert.ToDateTime(item["AlterTime"])
            //        });
            //    }
            //}
            return da;
        }

        /// <summary>
        /// 删除方法
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static int del(int uid)
        {
            string sql = "p_del";
            SqlParameter[] pram =
            {
                new SqlParameter("@UserID",uid)
            };
            return DBHelper.Number(sql, pram);
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Acount"></param>
        /// <param name="phones"></param>
        /// <param name="email"></param>
        /// <param name="roleid"></param>
        /// <param name="checktime"></param>
        /// <param name="isdelete"></param>
        /// <param name="aftertime"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static int updat(string UserName,string Acount,string Sex,string phones,string email,string roleid,int isdelete,int uid)
        {
            string sql = "p_update";
            SqlParameter[] pram =
            {
                new SqlParameter("@UserName",UserName),
                new SqlParameter("@Account",Acount),
                new SqlParameter("@Sex",Sex),
                new SqlParameter("@phone",phones),
                new SqlParameter("@Email",email),
                new SqlParameter("@RoleID",roleid),
                new SqlParameter("@IsDelete",isdelete),
                new SqlParameter("@UserID",uid)
            };
            return DBHelper.Number(sql, pram);
        }

        /// <summary>
        /// 绑定下拉列表
        /// </summary>
        /// <returns></returns>
        public static SqlDataReader doplie()
        {
            string sql = "p_rolenam";
            SqlParameter[] pram =
            {

            };
            return DBHelper.Exreader(sql, pram);
        }


        /// <summary>
        /// 添加用户
        /// </summary>
        /// <returns></returns>
        public static int Adduser(string uname,string sex,string acount,string phone,string pwd,string email,int roleid,string isdel)
        {
            string sql = "p_AddUser";
            SqlParameter[] pram =
            {
                new SqlParameter("@UserName",uname),
                new SqlParameter("@Sex",sex),
                new SqlParameter("@Acount",acount),
                new SqlParameter("@phone",phone),
                new SqlParameter("@pwd",pwd),
                new SqlParameter("@email",email),
                new SqlParameter("@roleid",roleid),
                new SqlParameter("@idelt",isdel)
            };
            return DBHelper.Number(sql, pram);
        }

        
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="paswod"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static int Uppwd(string paswod,int uid)
        {
            string sql = "p_updpwd";
            SqlParameter[] pram =
            {
                new SqlParameter("@password",paswod),
                new SqlParameter("@UserID",uid)
            };
            return DBHelper.Number(sql, pram);
        }

        public static object selpwd(int userid)
        {
            string sql = "select Password from UserInfo where UserID=@userid";
            SqlParameter[] pram =
            {
                new SqlParameter("@userid",userid)
            };
            object retur = DBHelper.GetObject(sql,pram);
            return retur;
        } 



        public static Users usertab(int Useid)
        {
            string sql = "p_user";
            SqlParameter[] pram =
            {
                new SqlParameter("@Userid",Useid)
            };
            SqlDataReader rd = DBHelper.Exreader(sql, pram);
           Users us = new Users();
            if (rd.HasRows)
            {
                while (rd.Read())
                {
                    
                    us.UserID = Convert.ToInt32(rd["UserID"]);
                    us.UserName = rd["UserName"].ToString();
                    us.sex = Convert.ToInt32(rd["Sex"]);
                    us.phone = rd["Phone"].ToString();
                    us.RoleName = rd["RoleName"].ToString();
                    us.Email = rd["Email"].ToString();
                    us.Account = rd["Account"].ToString();
                }
                rd.Close();
            }
            return us;
        }


        public SqlDataReader read()
        {
            string sql = "SELECT * FROM dbo.LogDic";
            SqlParameter[] pram =
            {
               
            };
            return DBHelper.getExreaders(sql,pram);
        }


        /// <summary>
        /// 日志页面查询
        /// </summary>
        /// <param name="log"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<SysLogs> sysBind(SysLogs log,int pageindex, int pagesize,out int count)
        {
            
            string TabName = "View_SysLogBind";
            string primary = "LogID";
            string Fierd = "1=1";
            if (log.TypeID != 0)
            {
                Fierd = Fierd + " and FK_TypeID=" + log.TypeID;
            }
            if (!string.IsNullOrWhiteSpace(log.Account))
            {
                Fierd = Fierd + " and Account='" + log.Account+"'";
            }
            if (!string.IsNullOrWhiteSpace(log.CheckInTime) && !string.IsNullOrWhiteSpace(log.CheckInTimeEnd))
            {
                Fierd = Fierd + " and SysLog.CheckInTime>='" + log.CheckInTime + "' and SysLog.CheckInTime<='" +log.CheckInTimeEnd+"'";
            }
            if (log.IsException!=-1)
            {
                Fierd = Fierd + " and IsException='" + log.IsException + "'";
            }
            if (!string.IsNullOrWhiteSpace(log.ProcName))
            {
                Fierd = Fierd + " and ProcName='" + log.ProcName + "'";
            }
            DataTable tab = PROC_Helper.Paging(TabName, primary, pageindex, pagesize, Fierd, out count);
            List<SysLogs> sys = new List<SysLogs>();
            if (tab.Rows.Count > 0)
            {
                foreach (DataRow item in tab.Rows)
                {
                    sys.Add(new SysLogs
                    {
                        Account = item["Account"].ToString(),
                        Behavior = item["Behavior"].ToString(),
                        CheckInTime = item["CheckInTime"].ToString(),
                        TypeID = Convert.ToInt32(item["FK_TypeID"]),
                        TypeName = item["TypeName"].ToString(),
                        Exception=item["Exception"].ToString(),
                        IsException=Convert.ToInt32(item["IsException"]),
                        FK_UserID=Convert.ToInt32(item["FK_UserID"]),
                        LogID=Convert.ToInt32(item["LogID"]),
                        ProcName=item["ProcName"].ToString(),
                        Parameters=item["Parameters"].ToString(),
                        IP=item["IP"].ToString(),
                        CheckInTimeEnd= item["CheckInTime"].ToString()

                    });
                }

            }
            return sys;
        }


        /// <summary>
        /// 修改页面绑定信息
        /// </summary>
        /// <returns></returns>
        public Users BindUser(int UserID)
        {
            string sql = "p_UpdateUser";
            SqlParameter[] pram =
            {
                new SqlParameter("@UserID",UserID)
            };
            Users use = new Users();
            SqlDataReader read = DBHelper.Exreader(sql, pram);
            if (read.HasRows)
            {
                while (read.Read())
                {
                    
                    use.Account = read["Account"].ToString();
                    use.RoleName = read["RoleName"].ToString();
                    use.RoleID = Convert.ToInt32(read["RoleID"]);
                    use.sex = Convert.ToInt32(read["sex"]);
                    use.phone = read["phone"].ToString();
                    use.UserName = read["UserName"].ToString();
                    use.Email = read["Email"].ToString();
                    use.IsDelete = Convert.ToInt32(read["IsDelete"]);
                    
                }
                read.Close();
            }
            return use;
        }

        /// <summary>
        /// 判断用户名和账号是否重复
        /// </summary>
        public int PDname(string UserName)
        {
            string sql = "SELECT COUNT(*) FROM dbo.UserInfo WHERE UserName=@uname";
            SqlParameter[] pram = 
            {
                new SqlParameter("@uname",UserName)
            };
            int a = Convert.ToInt32(DBHelper.GetObject(sql, pram));
            return a;
   
        }

        public int PDAccount(string account)
        {
            string sql = "SELECT COUNT(*) FROM dbo.UserInfo WHERE Account = @Account";
            SqlParameter[] pram =
            {
                new SqlParameter("@Account",account)
            };
            int a = Convert.ToInt32(DBHelper.GetObject(sql, pram));
            return a;
        }
    }
}

